MySQL经典练习题+解题思路（一） |
您所在的位置：网站首页 › mysql 练习 › MySQL经典练习题+解题思路（一） |
MySQL经典练习题+解题思路（一）
|
MySQL练习题（一）导入练习数据CREATE DATABASE `mysqlpractice`
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE DEPT
(DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
CREATE TABLE EMP
(EMPNO int(4) not null ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE(7,2),
COMM DOUBLE(7,2),
primary key (EMPNO),
DEPTNO INT(2)
)
;
CREATE TABLE SALGRADE
( GRADE INT,
LOSAL INT,
HISAL INT );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
commit;
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;导入成功后，有如下三张表： 员工表
部门表
工资等级表
（1）先按照部门编号分组，取得每个部门的最高薪水。 select e.ename,e.deptno,max(e.sal) '最高薪水' from emp e group by e.deptno;
（2）再将查询结果当做一张临时表，与emp表进行表连接（因为有的最高薪水是相同的人，需要将他们都显示出来） select e.ename,e.deptno,e.sal from (select ename,deptno,max(sal) as maxsal from emp group by deptno) t join emp e on t.deptno = e.deptno and e.sal = t.maxsal order by e.deptno;
（1）先找出每个部门的平均薪水 select deptno,avg(sal) from emp group by deptno;
（2）找出每个部门比各自部门平均薪水高的人 条件：比平均薪水高，部门号相同 select e.ename,e.sal,e.deptno from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t where e.deptno = t.deptno and e.sal > t.avgsal order by e.deptno;3、取得部门中（所有人的）平均的薪水等级（1）先找出每个人的薪资等级 select e.ename,s.grade,e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal;
（2）再按部门分组，求得每个部门的平均薪资水平 select t.ename,avg(t.grade),t.deptno from (select e.ename ename,s.grade grade,e.deptno deptno from emp e join salgrade s on e.sal between s.losal and s.hisal ) t group by t.deptno;
（1）将每个人的薪水进行降序排列，然后使用limit分页取第一个人的薪水 select e.ename,e.sal from emp e order by e.sal desc limit 0,1;（2）将结果当做一个临时表，与emp进行内连接 条件为emp表中的薪水 = 临时表中的薪水 select e.ename,e.sal from emp e join (select e.ename as ename,e.sal as sal from emp e order by e.sal desc limit 0,1 ) t on e.sal = t.sal;
（1）将emp表自连接，找出 emp a表中所有比 emp b表中薪资小的薪水生成一个结果 select distinct a.sal from emp a join emp b on a.sal < b.sal;
（2）再将emp中薪水不在这个结果中薪水找出来就是最高薪资 select e.ename,e.sal from emp e where e.sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal );
（1）取得每个部门的平均薪水 select deptno,avg(sal) from emp group by deptno;
（2）将结果当做一张临时表进行取最高 select t.deptno,max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno ) t;
（1）按部门分组求得每个组的平均薪水 select deptno ,avg(sal) from emp group by deptno;
（2）将结果与dept表进行内连接 select d.dname,t.deptno,max(t.avgsal) from (select deptno ,avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno;
（1）按部门分组求得每个组的平均薪水 select deptno ,avg(sal) from emp group by deptno;
（2）将结果与salgrade表进行内连接 求得每个部门的部门等级,并取最低等级的部门 select t.deptno,min(s.grade),t.avgsal from (select deptno ,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
（3）将结果与部门表进行内连接求得平均薪资水平最低的部门名称 select d.dname,t2.deptno,t2.avgsal,t2.mingrade from ( select t.deptno deptno,min(s.grade) mingrade,t.avgsal avgsal from (select deptno ,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal ) t2 join dept d on t2.deptno = d.deptno;
（1）找出所有是领导身份的员工代码 select distinct mgr from emp where mgr is not null;
（2）不在上面结果的员工都是普通员工，取工资最高的 select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
（3）找出比这个最高工资大的员工的姓名和编号 select e.ename,e.empno from emp e where( e.sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)) );
|
【本文地址】